# press F9 to execute the code
import pandas as pd
import numpy as np 

#create dataset manually
data = pd.DataFrame()
data["name"] = ["A","B","C","D","E","F","G","H","I","J"]
data["age"] = [22,9,5,39,50,17,26,33,43,48]
data["sex"] = ["F","M","M","F","F","M","F","M","F","M"]
data

data.dtypes# properties of columns in a dataframe
# object means a character, int64 and float are numeric 
data.get_dtype_counts() # count of different property;not so important
data.info()#information; not so important


import os# import library
#set the new path
os.chdir('C:/Users/Subhojit/Desktop/Python/Class 1')

data.to_csv('data.csv',index=False)#export data to local drive


# import dataset
undertaker = pd.read_csv('data.csv')# put the path and filename
undertaker# just print data

kane = undertaker.copy()#copy a data
kane



kane1 = kane.drop(data.columns[[0,1]],axis=1)#droping columns
kane1 = kane.drop(["age","sex"],axis=1)#droping columns by name

kane.drop([6,7],axis=0)# 6 & 7 row delete

## Filtering


# iloc means position based selection
#all rows & first 2 columns
data.iloc[:,[0,1]]# discrete numbers require []


# both row and columns together
data.iloc[[0,3],[0,1]]# discrete numbers require []
data.iloc[0:9,[0,1]] # range doesn't require [];selected rows (last number - 1)

data.iloc[3,2]# single number can be within bracket[] or without []
data.iloc[[3],[2]]# single number can be within bracket[] or without []

data.iloc[:,[0,2]]# selected columns and all rows

# without comma means only rows and all columns by default
data.iloc[0:3]# selected rows and all columns; selected rows (last number - 1)




# loc means name based selection
data.loc[:,["name","age"]]# both columns and rows together
data.loc[:,["sex","age"]]# both columns and rows together : means all
data.loc[0:4,["name","age"]]# both columns and rows together
data.loc[[0,4],["name","age"]]# non consecutive numbers

#loc name base selection of columns
data.loc[data["age"] > 39,["age","name","sex"]]#greater than
data.loc[data["age"] <= 39,["age","name","sex"]]#less than equals
data.loc[data["age"] != 39,]#not equals
data.loc[data["age"] == 39]#equals

data.loc[(data["age"] > 39) & (data["sex"] == "F")]
data.loc[(data.age > 39) & (data.sex == "F")]

data.loc[(data["age"] > 39) & (data.sex == "F"),["age","name"]]
data.loc[(data.age > 39) & (data.sex == "F"),["age","sex"]]# multiple columns
data.loc[(data.age > 39) & (data.sex == "F"),"age":"sex"]#range of columns
data.loc[(data.age > 39) | (data.sex == "F"),"age":"sex"]#or function
data.loc[~((data.age > 39) & (data.sex != "F")),"age":"sex"]#not equals

data.loc[data.name.isin(["A","B"]) ,"name":"sex"]#in function
data.loc[~(data["name"].isin(["A","B"]))]#not in function


# extra trick
## how to select continous and discrete number of columns
kane["new"] = kane.age+10
kane

r1 = pd.Series(range(0,2))#range last number - 1
r2 = pd.Series([3])#discrete number
final_range = r1.append(r2)#appending the nubers
kane.iloc[:,final_range]

rock = kane.copy()

rock["a1"] = "x"
rock["b2"] = "y"
rock["c3"] = "z"
rock["a2"] = "x1"
rock["b3"] = "y1"
rock["c4"] = "z1"
rock["b6"] = "y1"
rock["c7"] = "z1"
rock


# this is how you mention a range of numbers and 
#different numbers simulteniously, using np.r_[]
# this can be used for columns as well

data.ix[np.r_[0,3:6,9],]


## append - binding the rows
a = data.head(3)
a
b = data.iloc[5:8,[2,0,1]]
b

c = pd.concat([a,b],axis=0,ignore_index=True)#index will rearrange the index
c

#  sort=True will not sort the columns in alphabetical order 
# but it will work on pandas 0.23 and above
#pd.show_versions(as_json=False) # to check pandas version
c = b.append(a,ignore_index=True)
c

c = b.append(a,ignore_index=True,sort=False)
c

c = pd.concat([b,a],axis=0,sort=False,ignore_index=True)
c


# binding columns
s = pd.DataFrame()
s["new"] = range(6)
s

rock = pd.concat([c,s], axis=1)
rock